package com.br.mercarad.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import javax.swing.text.View;

import com.br.mercarad.db.vos.CampoVO;
import com.br.mercarad.db.vos.TabelaVO;
import com.br.mercarad.db.vos.ViewVO;

public class DbManagerOracle {
	Connection connection;

	// Statement statement;

	public DbManagerOracle() {
		try {

			Class.forName("oracle.jdbc.driver.OracleDriver");
			connection = newConnection();
			connection.setAutoCommit(false);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	private static Connection newConnection() {
		try {
			String url = "jdbc:oracle:thin:@10.100.10.14:1526:PROD";

			return DriverManager.getConnection(url, "meems", "mercanet");
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}

	public List<ViewVO> getViewsAndroid() {
		List<ViewVO> retorno = new ArrayList<ViewVO>();
		ViewVO view;
		try {
			Statement stat = connection.createStatement();

			ResultSet rs = stat.executeQuery("select * from svc_tabelas where id >=100");

			while (rs.next()) {
				view = new ViewVO();
				view.setId(rs.getInt("id"));
				view.setNome(rs.getString("tabela_correspondente"));
				view.setTabelaSqlite(rs.getString("tabela"));
				view.setParametro(rs.getString("campo_usuario"));
				retorno.add(view);
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return retorno;
	}

	public ViewVO getCamposViewAndroid(ViewVO view) {
		try {

			Statement stat = connection.createStatement();

			ResultSet rs = stat.executeQuery("select * from " + view.getNome() + " where rownum = 1");
			ResultSetMetaData metaData = rs.getMetaData();

			int rowCount = metaData.getColumnCount();

			System.out.println("Table Name : " + metaData.getTableName(2));
			System.out.println("Field  \tDataType");
			List<CampoVO> campos = new ArrayList<CampoVO>();
			CampoVO campo;

			for (int i = 0; i < rowCount; i++) {
				System.out.print(metaData.getColumnName(i + 1) + "  \t");
				System.out.print(metaData.getColumnLabel(i + 1) + "\t");
				System.out.print(metaData.getPrecision(i + 1) + "\t");
				System.out.print(metaData.getScale(i + 1) + "\t");
				System.out.print(metaData.getColumnType(i + 1) + "\t");
				System.out.print(metaData.getTableName(i + 1) + "\t");
				System.out.println(metaData.getColumnTypeName(i + 1));

				campo = new CampoVO();
				campo.setNome(metaData.getColumnLabel(i + 1));
				campo.setColumnName(metaData.getColumnName(i + 1));
				campo.setTypeOracle(metaData.getColumnTypeName(i + 1), metaData.getPrecision(i + 1),
						metaData.getScale(i + 1));
				campos.add(campo);

			}
			view.setCampos(campos);

			System.out.println();
		} catch (Exception e) {
			e.printStackTrace();
		} 
		return null;
	}

	public ViewVO getCamposViewAndroidNovo(ViewVO view) {
		try {

			Statement stat = connection.createStatement();

			ResultSet rs = stat
					.executeQuery("select C.*,MERCANETANDROID_METADATA.GET_TEXT(C.TABLE_NAME,C.COLUMN_NAME) COLUMN_NAME_CASE from USER_TAB_COLUMNS C "
							+ "WHERE C.TABLE_NAME =upper('" + view.getNome() + "') order by column_id asc");
			CampoVO campo;
			List<CampoVO> campos = new ArrayList<CampoVO>();
			while (rs.next()) {
				campo = new CampoVO();
				campo.setNome(rs.getString("COLUMN_NAME_CASE"));
				campo.setColumnName(rs.getString("COLUMN_NAME"));
				campo.setTypeOracle(rs.getString("DATA_TYPE"), rs.getInt("DATA_LENGTH"), rs.getInt("DATA_PRECISION"));
				campos.add(campo);

			}
			view.setCampos(campos);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	public void getSysdate() {
		try {
			Statement stat = connection.createStatement();

			ResultSet rs = stat.executeQuery("select sysdate from dual");
			ResultSetMetaData metaData = rs.getMetaData();

			int rowCount = metaData.getColumnCount();

			System.out.println("Table Name : " + metaData.getTableName(2));
			System.out.println("Field  \tDataType");

			for (int i = 0; i < rowCount; i++) {
				System.out.print(metaData.getColumnName(i + 1) + "  \t");
				System.out.println(metaData.getColumnTypeName(i + 1));
			}

			System.out.println();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

}
